package com.mzj.saas.commons;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class DBHelper {  
    
	public static Connection getDataConn(String dbName){
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://127.0.0.1:3309/"+dbName;
		String user="mzjuser_verification";
		String password="qAcvk@9-65AQP";
		
		//加载驱动程序
		try {
			Class.forName(driver);
			//System.out.println("驱动程序加载成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		//链接数据库
		Connection conn=null;
		try {
			conn=DriverManager.getConnection(url, user, password);
			if (!conn.isClosed()) {
				System.out.println("连接数据库成功");
			}
		} catch (SQLException e) {
			System.out.println("链接数据库失败: "+e.getMessage());
		}
		
		return conn;
	}
	
	/**
	 * 修改订单库订单表的商品的原始价格
	 */
	public static void updateOrderOriginPrice(){
		StringBuilder typeSql = new StringBuilder("SELECT pt.origin_price,pt.id,pt.product_id FROM product_types pt ");
		
		StringBuilder orderSql = new StringBuilder("SELECT DISTINCT (oi.product_type_id) FROM order_info oi");
		
		Connection typeCon =  (Connection) DBHelper.getDataConn("hx-product");
		Connection orderCon =  (Connection) DBHelper.getDataConn("hx-order");
		try {
			 PreparedStatement typePs = typeCon.prepareStatement(typeSql.toString());
			 ResultSet typeRs = typePs.executeQuery();
			 
			 List<Map<String,Object>> typeList = new ArrayList<Map<String,Object>>();//款式list
			
			 while(typeRs.next()){
				 Map<String,Object> map = new HashMap<String,Object>();
				 map.put("originPrice", typeRs.getString("origin_price"));
				 map.put("id", typeRs.getString("id"));
				 map.put("productId", typeRs.getString("product_id"));
				 typeList.add(map);
			 }
				
			 PreparedStatement orderPs = orderCon.prepareStatement(orderSql.toString());
			 ResultSet orderRs = orderPs.executeQuery();
			 
			 List<Map<String,Object>> orderList = new ArrayList<Map<String,Object>>();//订单list
			
			 while(orderRs.next()){
				 Map<String,Object> map = new HashMap<String,Object>();
				 map.put("productTypeId", orderRs.getString("product_type_id"));
				 orderList.add(map);
			 }
			 
			 List<String> updateList = new ArrayList<String>();
			
			 String updateSql = "";
			 for (Map<String, Object> order : orderList) {
				 for (Map<String, Object> type : typeList) {
					 if(order.get("productTypeId") != null && order.get("productTypeId") !="" && order.get("productTypeId").equals(type.get("id")) ){
						 updateSql = "update order_info set origin_price = "+type.get("originPrice") +" where product_type_id = "+type.get("id");
						 updateList.add(updateSql);
						 break;
					 }
				 }
			 }
			 Statement updatePs =(Statement) orderCon.createStatement();
			 for (String sql : updateList) {
				// System.out.println(sql);
				 updatePs.execute(sql);
			 }
			 
			 updatePs.close();
			 orderRs.close();
			 orderPs.close();
			 typeRs.close();
			 typePs.close();
			 typeCon.close();
			 orderCon.close();
			 
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	/*public static void main(String[] args) {
		updateOrderOriginPrice();
	}*/

}  
